﻿using ProductSystem.Model;
using org.in2bits.MyXls;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace ProductSystem.BLL.Common
{
    public delegate void FillExcelTh(Worksheet sheet, XF thXF, ref int row);
    public static class MyXlsHelper
    {
        #region 样式
        /// <summary>
        /// 字体水平对齐
        /// </summary>
        private static HorizontalAlignments horizontalAlignment = HorizontalAlignments.Centered;
        /// <summary>
        /// 字体竖向对齐
        /// </summary>
        private static VerticalAlignments verticalAlignment = VerticalAlignments.Centered;
        private static string fontName = "宋体";
        private static ushort titleFontHeight = 14 * 20;
        private static ushort thFontHeight = 12 * 20;
        private static ushort tdFontHeight = 12 * 20;
        private static int minWidth = 20;
        private static int maxWidth = 40;
        #endregion
        private static string nullValue = string.Empty;

        private static string GetStringValueOrDefault(this decimal? source)
        {
            return source.GetStringValueOrDefault(nullValue);
        }
        private static string GetStringValueOrDefault(this decimal? source, string defaultValue)
        {
            return source.HasValue ? source.Value.ToString() : defaultValue;
        }


        public static ExcelResult Export(DataTable dt, string title, FillExcelTh FillTh)
        {
            XlsDocument xls = new XlsDocument();
            #region 样式
            XF titleXF = xls.NewXF();
            titleXF.HorizontalAlignment = horizontalAlignment;
            titleXF.VerticalAlignment = verticalAlignment;
            titleXF.Font.FontName = fontName;
            titleXF.Font.Bold = true;
            titleXF.Font.Height = titleFontHeight;
            XF thXF = xls.NewXF();
            thXF.HorizontalAlignment = horizontalAlignment;
            thXF.VerticalAlignment = verticalAlignment;
            thXF.Font.FontName = fontName;
            thXF.Font.Bold = true;
            thXF.Font.Height = thFontHeight;
            XF tdXF = xls.NewXF();
            tdXF.HorizontalAlignment = horizontalAlignment;
            tdXF.VerticalAlignment = verticalAlignment;
            tdXF.Font.FontName = fontName;
            tdXF.Font.Bold = false;
            tdXF.Font.Height = tdFontHeight;
            #endregion
            #region 初始化sheet
            int colCount = dt.Columns.Count;
            Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");
            ColumnInfo colInfo = new ColumnInfo(xls, sheet);
            colInfo.ColumnIndexStart = 0;
            colInfo.ColumnIndexEnd = (ushort)(colCount - 1);
            int width = 255 / colCount;
            if (width < minWidth) width = minWidth;
            if (width > maxWidth) width = maxWidth;
            colInfo.Width = (ushort)(width * 256);
            sheet.AddColumnInfo(colInfo);
            #endregion
            #region title
            int row = 1;
            int col = 1;
            sheet.Cells.Add(row, col, title, titleXF);
            sheet.Cells.Merge(row, row, 1, colCount);
            #endregion
            #region th
            row++;
            if (FillTh == null)
            {
                col = 1;
                foreach (DataColumn item in dt.Columns)
                {
                    sheet.Cells.Add(row, col++, item.ColumnName, thXF);
                }
            }
            else
            {
                FillTh(sheet, thXF, ref row);
            }
            #endregion
            #region td
            foreach (DataRow rowItem in dt.Rows)
            {
                row++;
                col = 1;
                foreach (DataColumn colItem in dt.Columns)
                {
                    sheet.Cells.Add(row, col++, rowItem[colItem], tdXF);
                }
            }
            #endregion
            return new ExcelResult(title, xls.Bytes.ByteArray);
        }
        /// <summary>
        /// 中央气象台小时气象监测数据导出
        /// </summary>
        /// <param name="hourSpiDataList"></param>
        /// <param name="title"></param>
        /// <returns></returns>
        //public static ExcelResult ExportHourSpiData(List<Weather_H_SpiDatum> hourSpiDataList, string title)
        //{
        //    DataTable hourSpiDataDT = new DataTable();
        //    hourSpiDataDT.Columns.Add("城市", typeof(string));
        //    hourSpiDataDT.Columns.Add("时间", typeof(string));
        //    hourSpiDataDT.Columns.Add("风向(deg)", typeof(string));
        //    hourSpiDataDT.Columns.Add("风速(m/s)", typeof(string));
        //    hourSpiDataDT.Columns.Add("气压(hPa)", typeof(string));
        //    hourSpiDataDT.Columns.Add("气温(°C)", typeof(string));
        //    hourSpiDataDT.Columns.Add("降雨量(mm)", typeof(string));
        //    hourSpiDataDT.Columns.Add("湿度(%)", typeof(string));
        //    hourSpiDataDT.Columns.Add("体感温度(°C)", typeof(string));
        //    foreach (Weather_H_SpiDatum hourSpiData in hourSpiDataList)
        //    {
        //        DataRow hourSpiDataDR = hourSpiDataDT.NewRow();
        //        hourSpiDataDR["城市"] = hourSpiData.CityName;
        //        hourSpiDataDR["时间"] = hourSpiData.TimePoint.Value.ToString(TBConfig.Instance().HourDateTimeFormat);
        //        hourSpiDataDR["风向(deg)"] = hourSpiData.WindDirection.GetStringValueOrDefault();
        //        hourSpiDataDR["风速(m/s)"] = hourSpiData.WindSpeed.GetStringValueOrDefault();
        //        hourSpiDataDR["气压(hPa)"] = hourSpiData.AirPress.GetStringValueOrDefault();
        //        hourSpiDataDR["气温(°C)"] = hourSpiData.AirTemperature.GetStringValueOrDefault();
        //        hourSpiDataDR["降雨量(mm)"] = hourSpiData.RainFall.GetStringValueOrDefault();
        //        hourSpiDataDR["湿度(%)"] = hourSpiData.RelHumidity.GetStringValueOrDefault();
        //        hourSpiDataDR["体感温度(°C)"] = hourSpiData.Apparent.GetStringValueOrDefault();
        //        hourSpiDataDT.Rows.Add(hourSpiDataDR);
        //    }
        //    return Export(hourSpiDataDT, title, null);
        //}
    }
}
